#!bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi

DIM_ADS_PLATFORM_INDO_FULL_SQL="
WITH
    t1 AS (
        SELECT
            id, ad_id, platform_id, create_time, cancel_time
        FROM jtp_ads_warehouse.ods_ads_platform_full
        WHERE dt = '${data_date}'
    )
    ,t2 AS (
        SELECT
            id, product_id, material_id, group_id AS ad_group_id, ad_name, material_url
        FROM jtp_ads_warehouse.ods_ads_info_full
        WHERE dt = '${data_date}'
    )
    ,t3 AS (
        SELECT
            id, platform AS platform_name , platform_alias_zh
        FROM jtp_ads_warehouse.ods_ads_platform_info_full
        WHERE dt = '${data_date}'
    )
    ,t4 AS (
        SELECT
            id, name AS product_name , price AS product_price
        FROM jtp_ads_warehouse.ods_ads_product_info_full
        WHERE dt = '${data_date}'
    )
INSERT OVERWRITE TABLE jtp_ads_warehouse.dim_ads_platform_indo_full PARTITION (dt = '${data_date}')
SELECT
    t1.id
    ,t1.ad_id
    ,t2.ad_name
    ,t2.ad_group_id
    ,t2.product_id
    ,t4.product_name
    ,t4.product_price
    ,t2.material_id
    ,t1.platform_id
    ,t3.platform_name
    ,t3.platform_alias_zh
    ,t1.create_time
    ,t1.cancel_time
FROM t1
    LEFT JOIN t2 ON t1.ad_id = t2.id
    LEFT JOIN t4 ON t2.product_id = t4.id
    LEFT JOIN t3 ON t1.platform_id = t3.id
;
"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_ADS_PLATFORM_INDO_FULL_SQL}"